/**
 * 项目名：化工企业安全生产信息化管理平台
 * 日期：    2020-03-17 14:00:29
 * Copyright (c) 2015- -版权所有
 */
package com.joysuch.wwyt.edu.repository;

import com.joysuch.wwyt.edu.bean.EduTrainUserInfoDto;
import com.joysuch.wwyt.edu.entity.EduTrainningRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

import java.util.List;


/**
 * EduTrainningRecordDao
 *
 */
public interface EduTrainningRecordDao extends JpaRepository<EduTrainningRecord, Long>, QuerydslPredicateExecutor<EduTrainningRecord> {
    public Page<EduTrainningRecord> findAll(Specification<EduTrainningRecord> spec, Pageable pageable);

    public EduTrainningRecord save(EduTrainningRecord bean);

    public void delete(EduTrainningRecord bean);

    @Query("select count(m) from EduTrainningRecord m where code = ?1")
    long countOfCode(String code);

    @Query("select count(m) from EduTrainningRecord m where code = ?1 and id != ?2")
    long countOfCodeExcludeId(String code, Long id);

    @Query("select new com.joysuch.wwyt.edu.bean.EduTrainUserInfoDto(er.trainingName as trainName,er.trainningDate as trainTime," +
            "er.lessons as trainLearnHour,case eru.trainningResult when 'Y'then '合格' when 'N' then '不合格' else '' end as trainResult) " +
            "from EduTrainningRecord er left join EduTrainningRecordUser eru on er.id=eru.trainningRecordId " +
            "where er.deleteFlag='0' and eru.deleteFlag='0' and eru.userId=:userId " +
            "and eru.userContractorFlag=:userContractorFlag")
    Page<EduTrainUserInfoDto> userInfoTrain(Long userId, Pageable pageable, Integer userContractorFlag);

    List<EduTrainningRecord> findByTrainningPlanId(Long id);

    @Query(value = "select count(1) from wwyt_edu_trainning_record  where TRAINNING_PLAN_ID = ?1 and delete_flag = '0'", nativeQuery = true)
    Integer countByPlanId(Long planId);

    EduTrainningRecord findByCode(String code);
    @Query(value = "select * from wwyt_edu_trainning_record where EXAM_ID = ?1",nativeQuery = true)
    EduTrainningRecord selectExamId(Long id);
}
